import sys

from configparser import ConfigParser
from datetime import timedelta, datetime
from subprocess import Popen

from PyQt5.QtGui import QStandardItem, QStandardItemModel, QColor
from xlrd import open_workbook, xldate

from PyQt5.uic import loadUi
from PyQt5.QtWidgets import (QAbstractItemView, QDialog, QWidget, QMainWindow, QHeaderView)
from PyQt5.QtSql import QSqlQuery, QSqlDatabase, QSqlTableModel, QSqlQueryModel

from udf import *

conf = 'sys_config.ini'
config = ConfigParser()
config.read(conf, encoding='utf-8')
section = config.get('CURRENT_SECTION', 'section')
host = config.get(section, 'host')
port = config.getint(section, 'port')
db_name = config.get(section, 'db_name')

db = QSqlDatabase.addDatabase('QMYSQL')
db.setHostName(host)
db.setPort(port)
db.setDatabaseName(db_name)
db.setUserName('zjq')
db.setPassword('235689')
# db.setUserName('root')
# db.setPassword('123456')

query = QSqlQuery(db)
FIRST_ITEM = '全部'


def _cbox_popup(cbox, sql='', first_item=None, items=None):
    def process():
        cbox.clear()
        if first_item:
            cbox.addItem(first_item)
        if sql:
            if db.isOpen():
                query.exec(sql)
                while query.next():
                    cbox.addItem(query.value(0))
        else:
            assert isinstance(items, (list, tuple))
            cbox.addItems(items)
    return process


class MainForm(QMainWindow):
    """主操作界面"""
    uifile = 'ui/main_form.ui'

    def __init__(self):
        super(MainForm, self).__init__()
        self.config = ConfigParser()
        self.config.read(conf, encoding='utf-8')
        self.ui = loadUi(self.uifile)
        self.ui.closeEvent = self.closeEvent  # #exit app when this window quit
        self.ui.list_basedata.setSpacing(16)
        self.ui.list_main.setSpacing(16)
        self._company_name = self.config.get('SYS', 'company_name')
        self.ui.setWindowTitle(self._company_name)
        self.init_page0()
        self.current_op_type = None
        self.init_page1()
        self.init_page2()
        self.init_page3()
        self.ui.act_sys_setting.triggered.connect(self.sys_setting)
        self.ui.act_chgpwd.triggered.connect(self.chg_pwd)
        self.ui.act_adduser.triggered.connect(self.add_user)
        self.ui.list_basedata.itemClicked.connect(self.base_data)

    def closeEvent(self, event):
        """当此窗口(本程序主窗口)退出时，整个程序将退出（用来关闭过程中产生的子窗口）"""
        sys.exit(0)

    def init_page0(self):
        """铸件查询"""
        map_other_condition_keys = [['序列号', '订单号', '物料编码', '厂家'],
                                    ['serial_num', 'order_num', 'materiel.mtl_id', 'firm']]
        self.current_qry_wh_sql = ''
        self.p0_proxy_model = MySortFilterProxyModel()
        self.p0_query_model = QSqlQueryModel()
        self.p0_proxy_model.setSourceModel(self.p0_query_model)

        def query_wh():
            # parse query params
            graph_name = self.ui.p0_cbox_graph_id.currentText()
            graph_id = graph_name.split('|')[0]
            is_delivered = self.ui.p0_cbox_delivered.currentText()
            cbox_dt = self.ui.cbox_dt.currentText()
            line_dt = self.ui.line_dt.text()
            if line_dt:  # 如果用户输入 年/月 则搜索整月，如果输入 年 则搜索整年，年/月/日 仍搜索当天
                start_dt = ''
                end_dt = ''
                line_dts = line_dt.split('/')
                if len(line_dts) == 2:  # 年/月
                    start_dt = '/'.join(line_dts + ['1'])
                    end_dt = '/'.join(line_dts + ['31'])
                elif len(line_dts) == 1:
                    start_dt = '/'.join(line_dts + ['1', '1'])
                    end_dt = '/'.join(line_dts + ['12', '31'])
                else:
                    start_dt = '/'.join(line_dts[:3])
                    end_dt = '/'.join(line_dts[:3])
            other_condition = self.ui.p0_cbox_else_condition.currentIndex()
            other_contain = self.ui.p0_line_else_contain.text()
            conditions = ["warehouse.mtl_id=materiel.mtl_id"]
            if graph_id:
                conditions.append("graph_id LIKE '%{}%'".format(graph_id))
            if cbox_dt == '来料日期' and line_dt != '':
                conditions.append("coming_dt>='%s' and coming_dt<='%s'" % (start_dt, end_dt))
            elif cbox_dt == '发货日期' and line_dt != '':
                conditions.append("out_dt>='%s' and out_dt<='%s'" % (start_dt, end_dt))
            if is_delivered == "是":
                conditions.append("NOT ISNULL(out_dt)")
            elif is_delivered == "否":
                conditions.append("ISNULL(out_dt)")
            if other_condition != -1:
                fds = map_other_condition_keys[1]
                cdt = "{} LIKE '%{}%'".format(fds[other_condition], other_contain)
                conditions.append(cdt)
            sql_str = '''
                    SELECT
                    order_num 订单号,
                    serial_num 序列号,
                    firm 厂家,
                    materiel.mtl_id 物料编码,
                    graph_id 图号,
                    mtl_name 名称,
                    quantity 在手数量,
                    coming_dt 来料日期,
                    out_dt 发货日期,
                    (SELECT GROUP_CONCAT(CONCAT(process.process_name,'[',IF(status=0,'-',IF(status=1,'√','×')),']') 
                    SEPARATOR '        ') FROM process WHERE order_num=warehouse.order_num) 工序状态
                FROM
                    warehouse,
                    materiel
            '''
            if conditions:
                sql_str += "WHERE {}".format(' and '.join(conditions))
            self.p0_query_model.setQuery(sql_str + ' limit 0,100')
            self.ui.tbl_view.setModel(self.p0_proxy_model)

            if self.p0_query_model.rowCount() == 0:
                QMessageBox.information(self, '提示!', '共 0 条记录！', QMessageBox.Yes)
            else:
                self.current_qry_wh_sql = sql_str
                self.ui.line_pg_num.setText('1')

        def query_back_page():
            if self.current_qry_wh_sql:
                curr_page = int(self.ui.line_pg_num.text())
                if curr_page == 1:
                    pass
                else:
                    curr_page -= 1
                    self.ui.line_pg_num.setText(str(curr_page))
                    self.p0_query_model.setQuery(self.current_qry_wh_sql + ' limit {},100'.format((curr_page-1)*100))

        def query_next_page():
            if self.current_qry_wh_sql:
                curr_page = int(self.ui.line_pg_num.text())
                curr_page += 1
                self.ui.line_pg_num.setText(str(curr_page))
                self.p0_query_model.setQuery(self.current_qry_wh_sql + ' limit {},100'.format((curr_page-1)*100))

        def export_wh():
            filename = '{}_库存'.format(datetime.now().strftime('%Y%m%d%H%M'))
            export_model2xlsx(self.p0_query_model, filename)

        def trans_wh():
            model = self.ui.tbl_view.selectionModel()
            indexes = []
            if model:
                indexes = model.selectedRows()
            if indexes:
                add_out_dt = AddOutDt(indexes, self)
                if add_out_dt.ui.exec() == 1:
                    query_wh()

        def import_from_xls():
            xls_file = QFileDialog.getOpenFileName(caption='选择excel文件', filter='*.xlsx *.xls *.xlsm *.csv')
            fp = xls_file[0]
            if fp:
                choose_sht = ChooseSheet(fp, self.ui)
                choose_sht.ui.exec()

        def cancel_in():
            model = self.ui.tbl_view.selectionModel()
            indexes = []
            if model:
                indexes = model.selectedRows()
            if indexes:
                if QMessageBox.question(self, '提示!', '将要移除记录') == QMessageBox.Yes:
                    transaction_ret = []
                    query.exec('begin')
                    sql_1 = "DELETE FROM warehouse WHERE order_num='%s'"
                    sql_2 = "DELETE FROM process WHERE order_num='%s'"
                    for idx in indexes:
                        row = self.p0_proxy_model.mapToSource(idx).row()
                        rec = self.p0_query_model.record(row)
                        key = rec.value(0)
                        transaction_ret.append(query.exec(sql_1 % key))
                        transaction_ret.append(query.exec(sql_2 % key))
                    if all(transaction_ret):
                        query.exec('commit')
                        QMessageBox.information(self, '提示!', '已取消！', QMessageBox.Yes)
                    else:
                        query.exec('rollback')
                        QMessageBox.question(self, '错误', '未完成, 请重试！', QMessageBox.Yes)

        def cancel_out():
            model = self.ui.tbl_view.selectionModel()
            indexes = []
            if model:
                indexes = model.selectedRows()
            if indexes:
                if QMessageBox.question(self, '提示!', '确认取消出库？') == QMessageBox.Yes:
                    transaction_ret = []
                    query.exec('begin')
                    sql = "UPDATE warehouse SET out_dt=null WHERE order_num='%s'"
                    for idx in indexes:
                        row = self.p0_proxy_model.mapToSource(idx).row()
                        rec = self.p0_query_model.record(row)
                        key = rec.value(0)
                        transaction_ret.append(query.exec(sql % key))
                    if all(transaction_ret):
                        query.exec('commit')
                        QMessageBox.information(self, '提示!', '已取消！', QMessageBox.Yes)
                    else:
                        query.exec('rollback')
                        QMessageBox.question(self, '错误', '未完成, 请重试！', QMessageBox.Yes)

        def add_score():
            """生成工分更新窗口，实现按钮选取图号"""
            if self.ui.p0_cbox_graph_id.currentText():
                self.ui.p0_cbox_graph_id.setEnabled(False)
                score_info = AddScore(self)
                score_info.ui.show()

        def show_process_detail(index):
            if index.isValid():
                rec = self.p0_query_model.record(self.p0_proxy_model.mapToSource(index).row())
                order_num = rec.value(0)
                detail_dia = ProcessDetail(order_num)
                detail_dia.ui.exec()

        # self.ui.tbl_view.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.ui.tbl_view.horizontalHeader().setStretchLastSection(True)
        self.ui.tbl_view.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.ui.tbl_view.horizontalHeader().setHighlightSections(False)
        self.ui.tbl_view.doubleClicked.connect(show_process_detail)
        self.ui.p0_cbox_graph_id.popupNew.connect(_cbox_popup(
            self.ui.p0_cbox_graph_id, "SELECT CONCAT(graph_id,'|',mtl_name) FROM materiel"))
        self.ui.p0_cbox_else_condition.addItems(map_other_condition_keys[0])
        self.ui.p0_cbox_else_condition.setCurrentIndex(0)
        self.ui.p0_line_else_contain.setClearButtonEnabled(True)
        self.ui.btn_query.clicked.connect(query_wh)
        self.ui.btn_export.clicked.connect(export_wh)
        self.ui.btn_del.clicked.connect(trans_wh)
        self.ui.btn_import.clicked.connect(import_from_xls)
        self.ui.cancel_in.clicked.connect(cancel_in)
        self.ui.cancel_out.clicked.connect(cancel_out)
        self.ui.btn_add_score.clicked.connect(add_score)
        self.ui.btn_pg_back.clicked.connect(query_back_page)
        self.ui.btn_pg_next.clicked.connect(query_next_page)

    def init_page1(self):
        """工分查询"""
        self.p3_proxy_model = QSortFilterProxyModel()
        self.p3_query_model = QSqlQueryModel()
        self.p3_proxy_model.setSourceModel(self.p3_query_model)

        def query_score():
            sql_str = '''
            SELECT
                operator,
                mtl_name 名称,
                graph_id 图号,
                process_name 工序,
                sum(quantity) 数量,
                sum(total_score) 总工分,
                finish_dt 完成日期,
                GROUP_CONCAT(serial_num SEPARATOR ',') 序列号
            FROM
                (
                    SELECT
                        Concat(op.inner_id, '|', op.`name`) operator,
                        wpm.mtl_name,
                        wpm.serial_num,
                        wpm.process_name,
                        wpm.graph_id,
                        wpm.quantity,
                        wpm.score * wpm.quantity total_score,
                        wpm.finish_dt
                    FROM
                        (
                            SELECT
                                wp.*, m.graph_id,
                                m.mtl_name
                            FROM
                                (
                                    SELECT
                                        p.order_num,
                                        p.operator_id,
                                        p.score,
                                        p.process_name,
                                        p.finish_dt,
                                        w.serial_num,
                                        w.firm,
                                        w.mtl_id,
                                        w.quantity
                                    FROM
                                        process p
                                    LEFT JOIN warehouse w ON p.order_num = w.order_num
                                    WHERE
                                        p. STATUS <> 0
                                ) wp
                            LEFT JOIN materiel m ON wp.mtl_id = m.mtl_id
                        ) wpm
                    LEFT JOIN operator op ON wpm.operator_id = op.inner_id
                ) g
            GROUP BY
                operator,
                process_name,
                mtl_name,
                graph_id,
                finish_dt
            HAVING
            
            '''
            condition = []
            optr = self.ui.p3_cbox_optr.currentText()
            optr = '' if optr == FIRST_ITEM else optr
            dt_start = self.ui.p3_dt_start.date().toString('yyyy/MM/dd')
            dt_end = self.ui.p3_dt_end.date().toString('yyyy/MM/dd')
            if optr:
                condition.append("operator = '{}'".format(optr))
            condition.append("finish_dt >= '{}'".format(dt_start))
            condition.append("finish_dt <= '{}'".format(dt_end))
            sql_str += ' AND '.join(condition)
            self.p3_query_model.setQuery(sql_str)
            self.ui.p3_tbl_view.setModel(self.p3_proxy_model)

        def export_score():
            filename = '{}_工分记录'.format(datetime.now().strftime('%Y%m%d%H%M'))
            export_model2xlsx(self.p3_query_model, filename)

        self.ui.p3_tbl_view.horizontalHeader().setStretchLastSection(True)
        self.ui.p3_tbl_view.setSelectionBehavior(QAbstractItemView.SelectRows)
        self.ui.p3_tbl_view.horizontalHeader().setHighlightSections(False)
        self.ui.p3_tbl_view.verticalHeader().hide()
        self.ui.p3_cbox_optr.popupNew.connect(_cbox_popup(
            self.ui.p3_cbox_optr, "SELECT CONCAT(inner_id,'|',name) FROM operator", first_item=FIRST_ITEM))
        self.ui.p3_dt_start.setDate(datetime.now() - timedelta(30))
        self.ui.p3_dt_end.setDate(datetime.now())
        self.ui.p3_btn_query.clicked.connect(query_score)
        self.ui.p3_btn_export.clicked.connect(export_score)

    def init_page2(self):
        """进销存"""
        cols = ['日期', '图号', '来料数量', '发货数量', '本期结余', '上期结余']

        def get_all_dt(begin_date, end_date):
            date_list = []
            begin_date = datetime.strptime(begin_date, "%Y-%m-%d")
            end_date = datetime.strptime(end_date,"%Y-%m-%d")
            while begin_date <= end_date:
                date_str = begin_date.strftime("%Y-%m-%d")
                date_list.append(date_str)
                begin_date += timedelta(days=1)
            return date_list

        def query_jxc():
            end_dt = self.ui.jxc_dt_end.date().toString('yyyy-MM-dd')
            start_dt = self.ui.jxc_dt_start.date().toString('yyyy-MM-dd')
            all_days = get_all_dt(start_dt, end_dt)
            mtl = self.ui.jxc_gid.currentText()
            if mtl and mtl != "汇总":
                mtl = mtl.split('|')
                mtl_id = mtl[2]
                graph_id = mtl[0]
            else:
                mtl_id = ''
                graph_id = "--"
            qry_old_balance = '''
                SELECT
                    SUM(quantity)
                FROM
                    warehouse
                WHERE
                    (ISNULL(out_dt) or out_dt >= '{2}')
                AND mtl_id = '{0}'
                AND coming_dt < '{1}';
            '''
            qry_old_balance_all = '''
                SELECT
                    SUM(quantity)
                FROM
                    warehouse
                WHERE
                    (ISNULL(out_dt) or out_dt >= '{1}')
                AND coming_dt < '{0}';
            '''

            qry_whole_in = '''
                SELECT
                    coming_dt,
                    mtl_id,
                    sum(quantity)
                FROM
                    warehouse
                GROUP BY
                    coming_dt,
                    mtl_id
                HAVING
                    mtl_id = '{0}'
                AND coming_dt <= '{1}'
                AND coming_dt >= '{2}'
            '''
            qry_whole_in_all = '''
                SELECT
                    coming_dt,
                    sum(quantity)
                FROM
                    warehouse
                GROUP BY
                    coming_dt
                HAVING
                    coming_dt <= '{0}'
                AND coming_dt >= '{1}'
            '''

            qry_whole_out = '''
                SELECT
                    out_dt,
                    mtl_id,
                    sum(quantity)
                FROM
                    warehouse
                GROUP BY
                    out_dt,
                    mtl_id
                HAVING
                    mtl_id = '{0}'
                AND out_dt <= '{1}'
                AND out_dt >= '{2}'
            '''
            qry_whole_out_all = '''
                SELECT
                    out_dt,
                    sum(quantity)
                FROM
                    warehouse
                GROUP BY
                    out_dt
                HAVING
                    out_dt <= '{0}'
                AND out_dt >= '{1}'
            '''
            if all_days:
                nr = len(all_days)
                self.jxc_model.setRowCount(nr+1)
                whole_in, whole_out = {}, {}  # 用字典保存每个物件某天进 或 出的数量
                old_balance = 0
                if mtl_id:
                    query.exec(qry_old_balance.format(mtl_id, start_dt, start_dt))
                    if query.first():
                        old_balance = int(query.value(0))
                    query.exec(qry_whole_in.format(mtl_id, end_dt, start_dt))
                    while query.next():
                        whole_in[query.value(0).toString('yyyy-MM-dd')] = query.value(2)
                    query.exec(qry_whole_out.format(mtl_id, end_dt, start_dt))
                    while query.next():
                        whole_out[query.value(0).toString('yyyy-MM-dd')] = query.value(2)
                else:
                    query.exec(qry_old_balance_all.format(start_dt, start_dt))
                    if query.first():
                        old_balance = int(query.value(0))
                    query.exec(qry_whole_in_all.format(end_dt, start_dt))
                    while query.next():
                        whole_in[query.value(0).toString('yyyy-MM-dd')] = query.value(1)
                    query.exec(qry_whole_out_all.format(end_dt, start_dt))
                    while query.next():
                        whole_out[query.value(0).toString('yyyy-MM-dd')] = query.value(1)

                for r, day in enumerate(all_days):
                    qty_in = int(whole_in.get(day, 0))
                    qty_out = int(whole_out.get(day, 0))
                    qty_curr = old_balance + qty_in - qty_out
                    self.jxc_model.setItem(r, 0, QStandardItem(day))
                    self.jxc_model.setItem(r, 1, QStandardItem(graph_id))
                    self.jxc_model.setItem(r, 2, QStandardItem(str(qty_in)))
                    self.jxc_model.setItem(r, 3, QStandardItem(str(qty_out)))
                    self.jxc_model.setItem(r, 4, QStandardItem(str(qty_curr)))
                    self.jxc_model.setItem(r, 5, QStandardItem(str(old_balance)))
                    old_balance = qty_curr

                all_in = sum(whole_in.values())
                all_out = sum(whole_out.values())
                self.jxc_model.setItem(nr, 0, QStandardItem('汇总'))
                self.jxc_model.setItem(nr, 2, QStandardItem(str(int(all_in))))
                self.jxc_model.setItem(nr, 3, QStandardItem(str(int(all_out))))
                self.jxc_model.setItem(nr, 1, QStandardItem(''))
                self.jxc_model.setItem(nr, 4, QStandardItem(''))
                self.jxc_model.setItem(nr, 5, QStandardItem(''))

        def export_jxc():
            filename = '{}_进销存记录'.format(datetime.now().strftime('%Y%m%d%H%M'))
            export_model2xlsx(self.jxc_model, filename)
        
        self.ui.jxc_gid.popupNew.connect(_cbox_popup(
            self.ui.jxc_gid, "SELECT CONCAT(graph_id,'|',mtl_name,'|',mtl_id) FROM materiel", first_item='汇总'))
        self.ui.jxc_dt_start.setDate(datetime.now() - timedelta(30))
        self.ui.jxc_dt_end.setDate(datetime.now())
        self.jxc_model = QStandardItemModel()
        self.jxc_model.setHorizontalHeaderLabels(cols)
        self.ui.jxc_tbl.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.ui.jxc_tbl.setModel(self.jxc_model)
        self.ui.jxc_btn_qry.clicked.connect(query_jxc)
        self.ui.jxc_btn_export.clicked.connect(export_jxc)

    def init_page3(self):
        """某日结余"""
        self.jy_model = QSortFilterProxyModel()
        self.jy_query_model = QSqlQueryModel()
        self.jy_model.setSourceModel(self.jy_query_model)

        def qry_jy():
            dt = self.ui.jy_dt.date().toString('yyyy-MM-dd')
            sql = '''
                select
                    m.mtl_id 物料编码,
                    m.graph_id 图号,
                    m.mtl_name 名称,
                    ifnull(wh.quantity, 0) 数量,
                    m.m_desc 毛坯件物料描述
                from
                    materiel m
                left join (
                    select
                        t.mtl_id,
                        SUM(quantity) quantity
                    from
                        (
                        select
                            *
                        from
                            warehouse
                        where
                            (isnull(out_dt) or out_dt > '%s')
                            and coming_dt <= '%s') t
                    group by
                        t.mtl_id) wh on
                    m.mtl_id = wh.mtl_id
            ''' % (dt, dt)
            mtl_name = self.ui.jy_mtl_name.currentText()
            if mtl_name:
                sql += "WHERE m.mtl_name = '{}'".format(mtl_name)
            self.jy_query_model.setQuery(sql)
        
        def export_jy():
            dt = self.ui.jy_dt.date().toString('yyyyMMdd')
            filename = '{}_当日库存'.format(dt)
            export_model2xlsx(self.jy_query_model, filename)

        self.ui.jy_dt.setDate(datetime.now())
        self.ui.jy_mtl_name.popupNew.connect(_cbox_popup(
            self.ui.jy_mtl_name, "SELECT DISTINCT mtl_name FROM materiel"))
        self.ui.jy_tbl.horizontalHeader().setStretchLastSection(True)
        self.ui.jy_tbl.setModel(self.jy_model)
        self.ui.btn_jy.clicked.connect(qry_jy)
        self.ui.btn_export_jy.clicked.connect(export_jy)
    
    def set_user(self, user_info):
        """user_info: tuple(user_name, account)"""
        setattr(self, '_user', user_info)
        self.ui.cur_user_lbl.setText(user_info[0])

    def sys_setting(self):
        """run dialog in main form"""
        form = SysSetup(self.config, self)
        form.ui.exec()

    def chg_pwd(self):
        """run widget in main form"""
        form = ChgPwd(self)
        form.ui.show()

    def add_user(self):
        if self._user[1] == 'admin':  # only admin can add user
            form = AddUser(self)
            form.ui.show()
        else:
            QMessageBox.information(self, '权限提示', '请以管理员账号登录！', QMessageBox.Yes)

    def base_data(self, item):
        tbl_name = item.text()
        row = self.ui.list_basedata.currentRow()
        tbl_model = QSqlTableModel()
        tbl_model.setEditStrategy(QSqlTableModel.OnManualSubmit)
        if row == 0:
            tbl_model.setTable('operator')
            tbl_model.select()
            tbl_model.setHeaderData(0, Qt.Horizontal, '内部识别(同姓名区分)')
            tbl_model.setHeaderData(1, Qt.Horizontal, '姓名')
            tbl_model.setHeaderData(2, Qt.Horizontal, '默认工序(请与实际工序名一致)')
        elif row == 1:
            tbl_model.setTable('materiel')
            tbl_model.select()
            tbl_model.setHeaderData(0, Qt.Horizontal, '物料编码')
            tbl_model.setHeaderData(1, Qt.Horizontal, '图号')
            tbl_model.setHeaderData(2, Qt.Horizontal, '名称')
            tbl_model.setHeaderData(3, Qt.Horizontal, '毛坯描述')
            tbl_model.setHeaderData(4, Qt.Horizontal, '工序及工分')
        else:
            pass
        form = BaseData(self, (row, tbl_name), tbl_model)
        form.ui.show()


class AddUser(QWidget):
    uifile = 'ui/add_user.ui'

    def __init__(self, parent):
        super(AddUser, self).__init__(parent)
        self.ui = loadUi(self.uifile)
        self.ui.cbox_optype.addItems(['只可读取', '可读并可更改'])
        self.ui.cbox_optype.setCurrentIndex(0)
        self.ui.btn_submit.clicked.connect(self.on_submit)

    def on_submit(self):
        user_name = self.ui.line_username.text()
        account = self.ui.line_account.text()
        pwd = self.ui.line_password.text()
        opt_type = self.ui.cbox_optype.currentIndex()
        if user_name and account and pwd:
            saved_pwd = encrypt_pwd(pwd)
            if query.exec("INSERT INTO users (account, password, user_name, operate_type) "
                          "VALUES ('%s', '%s', '%s', %d);" % (account, saved_pwd, user_name, opt_type)):
                QMessageBox.information(self, '提示', '用户创建成功')
                self.ui.close()
            else:
                QMessageBox.warning(self, '操作失败', query.lastError().text(), QMessageBox.Yes)
        else:
            QMessageBox.warning(self, '操作失败', '缺少必要信息！', QMessageBox.Yes)


class ChgPwd(QWidget):
    uifile = 'ui/chg_pwd.ui'

    def __init__(self, parent):
        super(ChgPwd, self).__init__(parent=parent)
        self.account = parent._user[1]
        self.ui = loadUi(self.uifile)
        self.ui.btn_submit.clicked.connect(self.on_submit)

    def on_submit(self):
        new_pwd = self.ui.line_new_pwd.text()
        again_pwd = self.ui.line_again_pwd.text()
        if new_pwd != '' and new_pwd == again_pwd:
            if db.open():
                save_pwd = encrypt_pwd(new_pwd)
                if query.exec("UPDATE users SET password='%s' WHERE account='%s';" % (save_pwd, self.account)):
                    QMessageBox.information(self, '提示', '密码修改成功！')
                    self.ui.close()
                    db.close()
            else:
                QMessageBox.critical(self, '严重错误', '无法连接数据库！')
        else:
            QMessageBox.warning(self, '警告', '两次输入密码不一致')


class Login(QWidget):
    """登录窗口"""
    uifile = 'ui/sys_login.ui'

    def __init__(self, main_form):
        super(Login, self).__init__()
        self.ui = loadUi(self.uifile)
        self.main_form = main_form
        self.ui.btn_act.clicked.connect(self.on_act)

    def on_act(self):
        account = self.ui.line_user.text()
        pwd = self.ui.line_pwd.text()
        if not (account and pwd):
            QMessageBox.information(self, '提示', '缺少用户名或密码！', QMessageBox.Yes)
        else:
            db.open()
            pwd = encrypt_pwd(pwd)
            if query.exec("SELECT user_name, password, operate_type FROM users WHERE account='%s';" % account):
                if query.first():
                    user_name, correct_pwd, operate_type = query.value(0), query.value(1), query.value(2)
                    if pwd == correct_pwd:
                        self.main_form.set_user((user_name, account))
                        if operate_type == 0:
                            btns = [self.main_form.ui.btn_import,
                                    self.main_form.ui.btn_del,
                                    self.main_form.ui.cancel_in,
                                    self.main_form.ui.cancel_out,
                                    # self.main_form.ui.btn_add_score,
                                    self.main_form.ui.btn_del]
                            for btn in btns:
                                btn.setVisible(False)
                            self.main_form.ui.list_basedata.takeItem(0)
                            self.main_form.ui.list_basedata.takeItem(0)
                        self.ui.close()
                        self.main_form.ui.showMaximized()
                    else:
                        QMessageBox.question(self, '提示', '密码错误！', QMessageBox.Yes)
                else:
                    QMessageBox.question(self, '提示', '用户名错误！', QMessageBox.Yes)
            else:
                QMessageBox.critical(self, '严重错误', "连接数据库失败, 请重试!", QMessageBox.Yes)


class SysSetup(QDialog):
    """
    系统设置窗口 sys_config.ini
    """
    uifile = 'ui/sys_settings.ui'

    def __init__(self, config, parent):
        super(SysSetup, self).__init__()
        self.config = config
        self.parent = parent
        self.ui = loadUi(self.uifile)
        self.init_ui()
        self.ui.button_box.accepted.connect(self.on_accept)

    def init_ui(self):
        company_name = self.config.get('SYS', 'company_name')
        self.ui.line_cmy_name.setText(company_name)

    def on_accept(self):
        self.config.set('SYS', 'company_name', self.ui.line_cmy_name.text())
        with open(conf, 'w', encoding='utf-8') as f:
            self.config.write(f)
        # app should be reload when sys_config changed
        self.parent.ui.close()
        args = [sys.executable]
        if not getattr(sys, "frozen", False):  # PyInstaller error
            args.append("sys_run.py")
        Popen(args)


class BaseData(QWidget):
    uifile = 'ui/base_data.ui'

    def __init__(self, parent, tbl, table_model):  # tbl -> tuple(index, tbl_name)
        super(BaseData, self).__init__(parent)
        self.tbl_model = table_model
        self.tbl = tbl
        self.ui = loadUi(self.uifile)
        if tbl[0] == 0:
            self.ui.ff_name.setText('姓名:')
        elif tbl[0] == 1:
            self.ui.ff_name.setText('图号:')
        else:
            pass
        self.ui.lbl_item.setText(tbl[1])
        self.ui.tbl_view.setModel(table_model)
        self.ui.btn_append.clicked.connect(self.add_item)
        self.ui.btn_del.clicked.connect(self.del_item)
        self.ui.btn_submit.clicked.connect(self.submit)
        self.ui.btn_ff.clicked.connect(self.filter)

    def filter(self):
        if self.tbl[0] == 0:
            ff_name = "name"
        elif self.tbl[0] == 1:
            ff_name = "graph_id"
        else:
            ff_name = ''
        ff_value = self.ui.ff_value.text()
        self.tbl_model.setFilter("{} LIKE '%{}%'".format(ff_name, ff_value))

    def add_item(self):
        row = self.tbl_model.rowCount()
        self.tbl_model.insertRow(row)

    def del_item(self):
        index = self.ui.tbl_view.currentIndex()
        if index.isValid():
            self.tbl_model.removeRow(index.row())

    def submit(self):
        self.tbl_model.submitAll()
        self.tbl_model.select()


class ChooseSheet(QDialog):
    uifile = 'ui/get_sht.ui'

    def __init__(self, fp, parent):
        super(ChooseSheet, self).__init__()
        self.parent = parent
        self.ui = loadUi(self.uifile)
        self.wb = open_workbook(fp)
        self.ui.cbox_sht_name.addItems(self.wb.sheet_names())
        self.ui.btn_box.accepted.connect(self.on_accept)

    def on_accept(self):
        sht_name = self.ui.cbox_sht_name.currentText()
        if sht_name:
            sht = self.wb.sheet_by_name(sht_name)
            if sht.nrows > 1:
                query.exec("begin")
                sql_1 = "INSERT IGNORE INTO materiel VALUES ('%s', '%s', '%s', null, null)"  # todo materiel更改时注意
                sql_2 = "INSERT INTO warehouse(order_num,serial_num,firm,mtl_id,quantity,coming_dt) " \
                        "VALUES ('%s','%s','%s','%s', %d,'%s')"
                sql_3 = "INSERT INTO process(order_num, process_name) VALUES ('%s','%s')"
                for r in range(1, sht.nrows):
                    r_vals = sht.row_values(r)
                    ok1 = query.exec(sql_1 % (r_vals[1], r_vals[3], r_vals[4]))
                    ok2 = query.exec(sql_2 % (r_vals[0], r_vals[2], r_vals[6], r_vals[1], r_vals[5],
                                    xldate.xldate_as_datetime(r_vals[7], 0).strftime('%Y-%m-%d')))
                    ok3 = True
                    if len(r_vals) > 8:
                        s3 = []
                        for i in range(8, len(r_vals)):
                            if r_vals[i]:
                                s3.append(query.exec(sql_3 % (r_vals[0], r_vals[i])))
                        ok3 = all(s3)
                    if not (ok1 and ok2 and ok3):
                        query.exec('rollback')
                        QMessageBox.question(self, '失败', '格式或者数据冲突，请检查！', QMessageBox.Yes)
                        break
                else:
                    query.exec("COMMIT")
                    QMessageBox.information(self, '提示', '已导入')


class AddScore(QWidget):
    uifile = 'ui/add_score.ui'

    def __init__(self, parent):
        super(AddScore, self).__init__(parent)
        self.parent = parent
        self.ui = loadUi(self.uifile)
        self.ui.closeEvent = self.closeEvent
        self.ui.setWindowFlags(Qt.WindowStaysOnTopHint)
        self.graph_id = parent.ui.p0_cbox_graph_id.currentText().split('|')[0]
        query.exec("SELECT pro_score FROM materiel WHERE graph_id='%s'" % self.graph_id)
        self.pro_score = {}
        if query.first():
            pro_score_info = query.value(0)
            self.pro_score.update([ps.split('=') for ps in pro_score_info.split()])  # new method getting single score
            self.ui.cbox_pro_name.addItems(self.pro_score.keys())
            self.ui.cbox_pro_name.setCurrentIndex(-1)
        self.ui.curr_graph_id.setText(self.graph_id)
        query.exec("SELECT CONCAT(inner_id,'|',name) FROM operator")
        while query.next():
            self.ui.cbox_optr.addItem(query.value(0))
        self.ui.cbox_optr.setCurrentIndex(-1)
        self.ui.cbox_optr.currentTextChanged.connect(self.set_default_process_score)
        self.ui.finish_dt.setDate(datetime.now())
        self.ui.cbox_pro_name.currentTextChanged.connect(self.set_default_score)
        self.ui.btn_add_order_num.clicked.connect(self.add_order_nums)
        self.ui.btn_rm_order_num.clicked.connect(self.rm_order_nums)
        self.ui.btn_ok.clicked.connect(self.parse_score)

    def closeEvent(self, event):
        """当正处于工分更新状态时要使用户不能改变当前已选择的图号"""
        if not self.parent.ui.p0_cbox_graph_id.isEnabled():
            self.parent.ui.p0_cbox_graph_id.setEnabled(True)

    def add_order_nums(self):
        curr_items = set()
        for i in range(self.ui.lst_order_nums.count()):
            it_texts = self.ui.lst_order_nums.item(i).text().split()
            curr_items.add(' '.join(it_texts[:2]))
        model = self.parent.ui.tbl_view.selectionModel()
        indexes = []
        if model:
            indexes = model.selectedRows()
        if indexes:
            for index in indexes:
                rec = self.parent.p0_query_model.record(self.parent.p0_proxy_model.mapToSource(index).row())
                order_serial_num = '{} [{}]'.format(rec.value(0), rec.value(1))
                if order_serial_num not in curr_items:
                    curr_items.add(order_serial_num)
                    self.ui.lst_order_nums.addItem(order_serial_num)

    def rm_order_nums(self):
        for it in self.ui.lst_order_nums.selectedItems():
            self.ui.lst_order_nums.takeItem(self.ui.lst_order_nums.row(it))

    def set_default_score(self, p_str):
        default_score = self.pro_score.get(p_str, 0)
        self.ui.spin_score.setValue(int(default_score))
        # 增加检查是否该项目工分已被更新并设置红前景色醒目
        sql = "SELECT ISNULL(score) FROM process WHERE order_num='{}' AND process_name='{}'"
        for i in range(self.ui.lst_order_nums.count()):
            it = self.ui.lst_order_nums.item(i)
            it_txt = it.text()
            ls_it_txt = it_txt.split()
            order_num = ls_it_txt[0]
            query.exec(sql.format(order_num, p_str))
            ret = ''
            if query.first():
                if query.value(0) == 1:
                    ret = " <{}>待录入 √".format(p_str)
                    it.setForeground(QColor('black'))
                else:
                    ret = " <{}>已录过 ×".format(p_str)
                    it.setForeground(QColor('red'))
            it.setText(' '.join(ls_it_txt[:2]) + ret)

    def set_default_process_score(self, p_str):
        inner_id = p_str.split('|')[0]
        query.exec("SELECT default_process FROM operator WHERE inner_id='%s'" % inner_id)
        default_process = ''
        default_score = 0
        if query.first():
            default_process = query.value(0)
        if default_process and default_process in self.pro_score:
            self.ui.cbox_pro_name.setCurrentText(default_process)
            default_score = int(self.pro_score[default_process])
        else:
            self.ui.cbox_pro_name.setCurrentIndex(-1)
        self.ui.spin_score.setValue(default_score)

    def parse_score(self):
        """按列表的条目更新数据"""
        optr = self.ui.cbox_optr.currentText()
        pro_name = self.ui.cbox_pro_name.currentText()
        single_score = self.ui.spin_score.value()
        finish_dt = self.ui.finish_dt.date().toString('yyyy/MM/dd')
        status = self.ui.cbox_status.currentIndex()
        mark = self.ui.text_mark.toPlainText()
        if optr and pro_name:
            sql = "UPDATE process SET status={0},score={1},finish_dt='{2}',operator_id='{5}',mark='{6}' " \
                  "WHERE order_num='{3}' AND process_name='{4}'"
            if self.parent._user[1] != 'admin':  # 管理员账号可以更新已有工分的项目
                sql += ' AND ISNULL(score)'
            for i in range(self.ui.lst_order_nums.count()):
                it = self.ui.lst_order_nums.item(i)
                done_info = it.text()
                if self.parent._user[1] == 'admin' or it.text().endswith("√"):  # 满足更新条件后执行sql
                    it_texts = it.text().split()
                    order_num = it_texts[0]
                    if query.exec(sql.format(status, single_score, finish_dt, order_num,
                                             pro_name, optr.split('|')[0], mark)):
                        done_info = ' '.join(it_texts[:2]) + ' OK!'
                it.setText(done_info)


class ProcessDetail(QDialog):
    uifile = 'ui/process_detail.ui'

    def __init__(self, order_num):
        super(ProcessDetail, self).__init__()
        self.ui = loadUi(self.uifile)
        self.ui.detail_view.horizontalHeader().setStretchLastSection(True)
        model = QSqlQueryModel()
        sql = '''
            SELECT
                process_name 工序名,
                (SELECT CONCAT(inner_id,'|',name) FROM operator WHERE inner_id=operator_id) 操作者,
                IF(status=0,'等待中',IF(status=1,'正常的','异常的')) 状态,
                score 工分,
                finish_dt 完成日期,
                mark 备注
            FROM
                process
            WHERE
                order_num='{}'
            ORDER BY
                id
        '''
        model.setQuery(sql.format(order_num))
        self.ui.detail_view.setModel(model)


class AddOutDt(QDialog):
    uifile = 'ui/add_out_dt.ui'

    def __init__(self, indexes, parent):
        super().__init__(parent=parent)
        self.indexes = indexes
        self.parent = parent
        self.ui = loadUi(self.uifile)
        self.ui.dt.setDate(datetime.now())
        self.ui.btn_box.accepted.connect(self.on_accept)

    def on_accept(self):
        transaction_ret = []
        query.exec('begin')
        dt_now = self.ui.dt.date().toString('yyyy/MM/dd')
        sql = "UPDATE warehouse SET out_dt='%s' WHERE order_num='%s'"
        for idx in self.indexes:
            row = self.parent.p0_proxy_model.mapToSource(idx).row()
            rec = self.parent.p0_query_model.record(row)
            key = rec.value(0)
            transaction_ret.append(query.exec(sql % (dt_now, key)))
        if all(transaction_ret):
            query.exec('commit')
            QMessageBox.information(self, '提示!', '已完成出库！', QMessageBox.Yes)
        else:
            query.exec('rollback')
            QMessageBox.question(self, '错误', '未完成, 请重试！', QMessageBox.Yes)